﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Cosmetic.DataEntity;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Cosmetic.Logic
{
    /// <summary>
    /// user manager class
    /// </summary>
    public class UserManager : LogicBase
    {
        private CategoryManager m_CategoryManager = new CategoryManager();

        /// <summary>
        /// Insert a new User into database
        /// </summary>
        /// <param name="aUser">new User</param>
        public void Insert(User aUser)
        {
            m_CosmeticDC.Users.InsertOnSubmit(aUser);
            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// Delete User
        /// </summary>
        /// <param name="aUser">The User want delete</param>
        public void Delete(User aUser)
        {
            m_CosmeticDC.Users.DeleteOnSubmit(aUser);
            m_CosmeticDC.SubmitChanges();
        }


        /// <summary>
        /// 获取所有邮箱地址
        /// </summary>
        /// <param name="aUser">new User</param>
        public List<email> getallmial()
        {
            return (from theActivity in m_CosmeticDC.email
                    select theActivity).ToList();
        }
        /// <summary>
        /// 根据ID获取邮箱
        /// </summary>
        /// <param name="aUser">new User</param>

        public email getemial(int id)
        {
            return m_CosmeticDC.email.Single(theProect => theProect.id == id);
        }


        /// <summary>
        /// 获取用户的全部信息
        /// </summary>
        /// <param name="aUser">new User</param>
        public List<User> GetAlles1()
        {
            return (from theActivity in m_CosmeticDC.Users
                    orderby theActivity.RegisterTime descending
                    select theActivity).ToList();
        }
        /// <summary>
        /// 获取用户的手机号码获取用户信息
        /// </summary>
        /// <param name="aUser">new User</param>
        public List<User> GetAlles(string a)
        {
            return (from theActivity in m_CosmeticDC.Users
                    where theActivity.tel  == a
                    orderby theActivity.RegisterTime descending
                    select theActivity).ToList();
        }
        public User GetUserById(Guid? aUserId)
        {
            List<User> theUserList = (from theUser in m_CosmeticDC.Users
                                      where theUser.UserId == aUserId
                                      select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }

        public static void UpdateInformation(Guid  id, decimal  a )
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = string.Empty;

                cmdStr = "UPDATE [User]  SET [Balance] ='" + a + "'  WHERE UserId='" + id + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }


        public static void UpdateInformations(Guid id, decimal a,string bb)
        {
            string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = string.Empty;

                cmdStr = "UPDATE [User]  SET [Balance] ='" + a + "',[Level]='" + bb + "'  WHERE UserId='" + id + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
        //public UserInfo  GetUserById1(Guid? aUserId)
        //{
        //    List<UserInfo > theUserList = (from theUser in m_CosmeticDC.UserInfos 
        //                              where theUser.UserId == aUserId
        //                              select theUser).ToList();
        //    if (theUserList.Count == 1)
        //    {
        //        return theUserList[0];
        //    }
        //    return null;
        //}

        //public static void UpdateInformation(Guid  id, decimal b)
        //{
        //    string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
        //    SqlConnection con = new SqlConnection(conStr);
        //    try
        //    {
        //        con.Open();
        //        string cmdStr = string.Empty;

        //        cmdStr = "UPDATE [User]  SET [Balance] ='" + b + "'  WHERE UserId=" + id + "";
        //        SqlCommand com = new SqlCommand(cmdStr, con);
        //        com.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}
        /// <summary>
        /// get user by user email,if the email already register return this user else return null
        /// </summary>
        /// <param name="anEmail">user email</param>
        /// <returns>if the email already register, return the user info, else return null</returns>
        public User GetUserByEmail(string anEmail)
        {
            List<User> theUserList = (from theUser in m_CosmeticDC.Users
                                      where theUser.Email == anEmail
                                      select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }

       


        public User GetUserByName(string aName)
        {
            List<User> theUserList = (from theUser in m_CosmeticDC.Users
                                      where theUser.UserName == aName
                                      select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }

        /// <summary>
        /// 根据手机号码获取详细信息
        /// </summary>
        /// <param name="anEmail">user email</param>
        /// <returns>if the email already register, return the user info, else return null</returns>
        public User GetUserBytel(string tel)
        {
            List<User> theUserList = (from theUser in m_CosmeticDC.Users
                                      where theUser.tel  == tel
                                      select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }

        /// <summary>
        /// get user by user name,if the user name already register return this user else return null
        /// </summary>
        /// <param name="aUserName">user name</param>
        /// <returns>if the user name already register, return the user info, else return null</returns>
        //public User GetUserByUserName(string aUserName)
        //{
        //    List<User> theUserList = (from theUser in m_CosmeticDC.Users
        //                              where theUser.UserName.ToLower() == aUserName.ToLower()
        //                              select theUser).ToList();
        //    if (theUserList.Count == 1)
        //    {
        //        return theUserList[0];
        //    }
        //    return null;
        //}

        /// <summary>
        ///
        /// </summary>
        /// <param name="aIntergral"></param>
        //public void SetIntegral(User aUser)//decimal aIntergral)
        //{
        //    //update by sqlcommand(failed by linq)
        //    m_CosmeticDC.ExecuteCommand("UPDATE [dbo].[User] SET Integral=@p0 WHERE UserId = @p1", aUser.Integral, aUser.UserId);
        //}
        /// <summary>
        /// check the user login, if not have the user or Email, Password error return null, else return the user entity;
        /// </summary>
        /// <param name="aName"></param>
        /// <param name="aPassword"></param>
        /// <returns></returns>
        public User LoginValidate(string aName, string aPassword)
        {
            List<User> theUserList = (from theUser in m_CosmeticDC.Users
                                      where theUser.tel == aName && theUser.Password == aPassword
                                      select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }


        public User loginValidate1( string aName,string aPassword)
        {
            List <User >  theUserLists=( from theUser in m_CosmeticDC.Users 
                                         where theUser .UserName ==aName && theUser .Password == aPassword 
                                          select theUser ).ToList ();
            if (theUserLists.Count == 1)
            {
                return theUserLists[0];
            }
            return null;
        }

        public User LoginValidateByEmail(string aEmail, string aPassword)
        {
            List<User> theUserList = (from theUser in m_CosmeticDC.Users
                                      where theUser.Email == aEmail && theUser.Password == aPassword
                                      select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }

        /// <summary>
        /// 输入手机号码判断是否有此用户
        /// </summary>
        /// <param name="aName"></param>
        /// <param name="aPassword"></param>
        /// <returns></returns>
        public User LoginValidateBytel(string tel)
        {
            List<User> theUserList = (from theUser in m_CosmeticDC.Users
                                      where theUser.tel  == tel 
                                      select theUser).ToList();
            if (theUserList.Count == 1)
            {
                return theUserList[0];
            }
            return null;
        }
        public List<User> GetAll()
        {
            return m_CosmeticDC.Users.ToList();
        }

        /// <summary>
        /// check aUserFriend is or isnot aUser's friend. 
        /// True: is friend, False: isnt friend
        /// </summary>
        /// <param name="aUser">user</param>
        /// <param name="aUserFriend">friend</param>
        /// <returns>True: is friend, False: isnt friend</returns>
        public bool CheckFriend(User aUser, User aUserFriend)
        {
            return this.CheckFriend(aUser.UserId, aUserFriend.UserId);
        }


        //public bool CheckExists(string uername)
        //{
        //    List<User > theProductList = (from theProduct in m_CosmeticDC.Users
        //                                  where theProduct.UserName == uername
        //                                   select theProduct).ToList();
        //    return theProductList.Count > 0;
        //}
        /// <summary>
        /// check aUserFriend is or isnot aUser's friend. 
        /// True: is friend, False: isnt friend
        /// </summary>
        /// <param name="aUserId">user id</param>
        /// <param name="aUserFriendId">user friend id</param>
        /// <returns>True: is friend, False: isnt friend</returns>
        public bool CheckFriend(Guid aUserId, Guid aUserFriendId)
        {
            List<Friend> theFriendList = (from theFriend in m_CosmeticDC.Friends
                                          where theFriend.UserId == aUserId && theFriend.FriendId == aUserFriendId
                                          select theFriend).ToList();

            return !(theFriendList.Count == 0);
        }

        /// <summary>
        /// 获得用户详细信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        //public UserInfo GetUserInfo(User user)
        //{
        //    if (user == null)
        //    {
        //        return null;
        //    }
        //    var result = from userinfo in m_CosmeticDC.UserInfos
        //                 where userinfo.UserId == user.UserId
        //                 select userinfo;
        //    if (result.Count() != 0)
        //    {
        //        return result.Single();
        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}

        public void UseDiscountCard(string Id)
        {

        }


        public List<DiscountCard> GetNotUsedDiscountCardByUserId(Guid UserId)
        {
            var result = (from dis in m_CosmeticDC.DiscountCards
                          where dis.UserId == UserId && dis.IsUsed == false
                          select dis).ToList();
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public List<DiscountCard> GetDiscountCardByUserId(Guid UserId)
        {
            var result = (from dis in m_CosmeticDC.DiscountCards
                          where dis.UserId == UserId
                          select dis).ToList();
            return result;
        }
        public DiscountCard GetDiscountCardById(string Id)
        {
            DiscountCard result = (from dis in m_CosmeticDC.DiscountCards
                                   where dis.Id == Id
                                   select dis).Single();
            return result;
        }

        public decimal GetAccConsume(Guid UserId)
        {
            List<Order> result = (from ord in m_CosmeticDC.Orders
                                  where ord.UserId == UserId
                                  select ord).ToList();
            decimal SumConsume = 0;

            for (int i = 0; i < result.Count; i++)
            {
                SumConsume += result[i].Price.Value;
            }

            return SumConsume;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public decimal GetAccBack(Guid UserId)
        {
            List<Order> result = (from ord in m_CosmeticDC.Orders
                                  where ord.UserId == UserId && ord.State == "取消"
                                  select ord).ToList();

            decimal SumConsume = 0;

            for (int i = 0; i < result.Count; i++)
            {
                SumConsume += result[i].Price.Value;
            }
            return SumConsume;
        }

        public enum Duration
        {
            week = 1,
            month = 2,
            month3 = 3,
            halfyear = 4
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public IEnumerable GetAllProductsBroughtByUserId(Guid UserId, Duration duration)
        {
            if (duration == Duration.week)
            {
                //var result = from ord in m_CosmeticDC.Orders
                //             join ordDetail in m_CosmeticDC.OrderDetails
                //             on ord.OrderId equals ordDetail.OrderId
                //             where ord.State=="已完成" && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddDays(-7)
                //             select new
                //             {
                //                 ProductName = ordDetail.Product.Name,
                //                 PurchaseDagte = ord.CreateTime,
                //                 PurchaseAmount = ord.Price,
                //                 ProductCode = ordDetail.ProductCode
                //             };
                //return result;
                var result = from ord in m_CosmeticDC.Orders
                             join ordDetail in m_CosmeticDC.OrderDetails
                             on ord.OrderId equals ordDetail.OrderId
                             where ord.State == "已完成"&& ordDetail.PackageId ==null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddDays(-7)
                             select new
                             {
                                 ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == ordDetail.ProductId select theProduct).First().Name,
                                 PurchaseDagte = ord.CreateTime,
                                 PurchaseAmount = ord.Price,
                                 ProductCode = ordDetail.ProductCode
                             };
                //推荐搭配的商品1
                var result2 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddDays(-7)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品2
                var result3 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddDays(-7)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品3
                var result4 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddDays(-7)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                return result;
                //return result.Concat(result2).Concat(result3).Concat(result4);
            }
            else if (duration == Duration.month)
            {
                //var result = from ord in m_CosmeticDC.Orders
                //             join ordDetail in m_CosmeticDC.OrderDetails
                //             on ord.OrderId equals ordDetail.OrderId
                //             where ord.State=="已完成" && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-1)
                //             select new
                //             {
                //                 ProductName = ordDetail.Product.Name,
                //                 PurchaseDagte = ord.CreateTime,
                //                 PurchaseAmount = ord.Price,
                //                 ProductCode = ordDetail.ProductCode
                //             };

                var result = from ord in m_CosmeticDC.Orders
                             join ordDetail in m_CosmeticDC.OrderDetails
                             on ord.OrderId equals ordDetail.OrderId
                             where ord.State == "已完成" && ordDetail.PackageId == null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-1)
                             select new
                             {
                                 ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == ordDetail.ProductId select theProduct).First().Name,
                                 PurchaseDagte = ord.CreateTime,
                                 PurchaseAmount = ord.Price,
                                 ProductCode = ordDetail.ProductCode
                             };
                //推荐搭配的商品1
                var result2 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-1)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品2
                var result3 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-1)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品3
                var result4 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-1)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };

                //return result.Concat(result2).Concat(result3).Concat(result4);
                return result;
                //return null;
            }
            else if (duration == Duration.month3)
            {
                //var result = from ord in m_CosmeticDC.Orders
                //             join ordDetail in m_CosmeticDC.OrderDetails
                //             on ord.OrderId equals ordDetail.OrderId
                //             where ord.State == "已完成" && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-3)
                //             select new
                //             {
                //                 ProductName = ordDetail.Product.Name,
                //                 PurchaseDagte = ord.CreateTime,
                //                 PurchaseAmount = ord.Price,
                //                 ProductCode = ordDetail.ProductCode
                //             };
                //return result;

                var result = from ord in m_CosmeticDC.Orders
                             join ordDetail in m_CosmeticDC.OrderDetails
                             on ord.OrderId equals ordDetail.OrderId
                             where ord.State == "已完成" && ordDetail.PackageId == null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-3)
                             select new
                             {
                                 ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == ordDetail.ProductId select theProduct).First().Name,
                                 PurchaseDagte = ord.CreateTime,
                                 PurchaseAmount = ord.Price,
                                 ProductCode = ordDetail.ProductCode
                             };
                //推荐搭配的商品1
                var result2 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-3)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品2
                var result3 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-3)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品3
                var result4 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-3)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };

                //return result.Concat(result2).Concat(result3).Concat(result4);
                return result;
                //return null;
            }
            else if (duration == Duration.halfyear)
            {
                //var result = from ord in m_CosmeticDC.Orders
                //             join ordDetail in m_CosmeticDC.OrderDetails
                //             on ord.OrderId equals ordDetail.OrderId
                //             where ord.State == "已完成" && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-6)
                //             select new
                //             {
                //                 ProductName = ordDetail.Product.Name,
                //                 PurchaseDagte = ord.CreateTime,
                //                 PurchaseAmount = ord.Price,
                //                 ProductCode = ordDetail.ProductCode
                //             };
                //return result;
                var result = from ord in m_CosmeticDC.Orders
                             join ordDetail in m_CosmeticDC.OrderDetails
                             on ord.OrderId equals ordDetail.OrderId
                             where ord.State == "已完成" && ordDetail.PackageId == null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-6)
                             select new
                             {
                                 ProductName = "",// (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == ordDetail.ProductId select theProduct).First().Name,
                                 PurchaseDagte = ord.CreateTime,
                                 PurchaseAmount = ord.Price,
                                 ProductCode = ordDetail.ProductCode
                             };
                //推荐搭配的商品1
                var result2 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-6)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId1 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品2
                var result3 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-6)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId2 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };
                //推荐搭配的商品3
                var result4 = from ord in m_CosmeticDC.Orders
                              join ordDetail in m_CosmeticDC.OrderDetails
                              on ord.OrderId equals ordDetail.OrderId
                              join thePackage in m_CosmeticDC.PackageSales
                              on ordDetail.PackageId equals thePackage.Id
                              where ord.State == "已完成" && ordDetail.PackageId != null && ord.UserId == UserId && ord.CreateTime < DateTime.Today && ord.CreateTime > DateTime.Today.AddMonths(-6)
                              select new
                              {
                                  ProductName = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().Name,
                                  PurchaseDagte = ord.CreateTime,
                                  PurchaseAmount = (from theProduct in m_CosmeticDC.ProductInfos where theProduct.ProductId == thePackage.ProductId3 select theProduct).First().网络零售价,
                                  ProductCode = ordDetail.ProductCode
                              };

                //return result.Concat(result2).Concat(result3).Concat(result4);
                return result;
            }
            else
            {
                return null;
            }

        }




        #region Message

        /// <summary>
        /// send new message
        /// </summary>
        /// <param name="aMessage">the new message</param>
        public void SendMessage(UserMessage aMessage)
        {
            m_CosmeticDC.UserMessages.InsertOnSubmit(aMessage);
            m_CosmeticDC.SubmitChanges();
        }
        /// <summary>
        /// get use's friend by use id
        /// </summary>
        /// <param name="aUserId">the use id</param>
        /// <returns>friend list</returns>
        public List<User> GetUserFriend(Guid aUserId)
        {
            return (from theFriend in m_CosmeticDC.Friends
                    where theFriend.UserId == aUserId
                    select theFriend.UserFriend).ToList();
        }

        /// <summary>
        /// get user message by receive user id
        /// </summary>
        /// <param name="aReceiveUserId">receive user id</param>
        /// <returns>message list</returns>
        public List<UserMessage> GetMessageByReceiveUser(Guid aReceiveUserId)
        {
            return (from theMessage in m_CosmeticDC.UserMessages
                    where theMessage.ReceiveUserId == aReceiveUserId
                    orderby theMessage.SendTime descending
                    select theMessage).ToList();
        }

        /// <summary>
        /// get user message by user message id
        /// </summary>
        /// <param name="aUserMessgeId">user message id</param>
        /// <returns>user message</returns>
        //public usermessage getmessagebyid(guid ausermessgeid)
        //{
        //    return (from themessage in m_cosmeticdc.usermessages
        //            where themessage.usermessageid == ausermessgeid
        //            select themessage).singleordefault();
        //}

        public Message GetMessageById(Guid Id)
        {
            Message result = (from message in m_CosmeticDC.Messages
                              where message.Id == Id
                              select message).Single();
            return result;
        }

        public void DeleteMessageById(Guid Id)
        {
            Message Ondelete = (from message in m_CosmeticDC.Messages
                                where message.Id == Id
                                select message).Single();
            m_CosmeticDC.Messages.DeleteOnSubmit(Ondelete);
            m_CosmeticDC.SubmitChanges();
        }

        /// <summary>
        /// delete user message by message id
        /// </summary>
        /// <param name="aUserMessgeId">user message id</param>
        //public void DeleteMessage(Guid aUserMessgeId)
        //{
        //    m_CosmeticDC.UserMessages.DeleteOnSubmit(this.GetMessageById(aUserMessgeId));
        //    m_CosmeticDC.SubmitChanges();
        //}

        #endregion
        #region User Favorite
        /// <summary>
        /// get favorite by favrotie id
        /// </summary>
        /// <param name="aFavoriteId">favorite id</param>
        /// <returns>favorite</returns>
        public Favorite GetFavoriteById(Guid aFavoriteId)
        {
            return m_CosmeticDC.Favorites.FirstOrDefault(theFavorite => theFavorite.FavoriteId == aFavoriteId);
        }
        public Favorite GetFavoriteById(Guid aUserId, Guid aProductId)
        {
            return m_CosmeticDC.Favorites.FirstOrDefault(theFavorite => theFavorite.UserId == aUserId && theFavorite.ProductId == aProductId);
        }

        /// <summary>
        /// insert favorite into db 
        /// </summary>
        /// <param name="aFavorite">new favorite</param>
        public void AddFavorite(Favorite aFavorite)
        {
            m_CosmeticDC.Favorites.InsertOnSubmit(aFavorite);
            m_CosmeticDC.SubmitChanges();
        }
        /// <summary>
        /// delete favorite
        /// </summary>
        /// <param name="aFavoriteId">favorite id</param>
        public void DeleteFavorite(Guid aFavoriteId)
        {
            m_CosmeticDC.Favorites.DeleteOnSubmit(GetFavoriteById(aFavoriteId));
            m_CosmeticDC.SubmitChanges();
        }

        public void DeleteFavorite(Guid aUserId, Guid aProductId)
        {
            m_CosmeticDC.Favorites.DeleteOnSubmit(GetFavoriteById(aUserId, aProductId));
            m_CosmeticDC.SubmitChanges();
        }

        #endregion
        #region Address

        public void InsertAddress(Address anAdress)
        {
            m_CosmeticDC.Address.InsertOnSubmit(anAdress);
            m_CosmeticDC.SubmitChanges();
        }

        public void DeleteAddress(Address anAddress)
        {
            m_CosmeticDC.Address.DeleteOnSubmit(anAddress);
            m_CosmeticDC.SubmitChanges();
        }
        public void DeleteAddress(Guid anAddressId)
        {
            this.DeleteAddress(this.GetAddressById(anAddressId));
        }

        public Address GetAddressById(Guid anAddressId)
        {
            return m_CosmeticDC.Address.FirstOrDefault(theAddress => theAddress.AddressId == anAddressId);
        }

        public List<Address> GetAddressByUser(Guid aUserId)
        {
            return (from theAddress in m_CosmeticDC.Address
                    where theAddress.UserId == aUserId
                    select theAddress).ToList();
        }

        #endregion

        public SalesPromotion GetSalePromotion(Guid UserId)
        {
            var result = from sale in m_CosmeticDC.SalesPromotions
                         where sale.UserId == UserId
                         select sale;

            if (result == null || result.Count() == 0)
            {
                SalesPromotion newsale = new SalesPromotion();
                newsale.UserId = UserId;
                newsale.AcceptMail = false;
                newsale.Book = false;
                newsale.Children = false;
                newsale.Cloth = false;
                newsale.Cosmetic = false;
                newsale.Cyber = false;
                newsale.House = false;
                m_CosmeticDC.SalesPromotions.InsertOnSubmit(newsale);
                m_CosmeticDC.SubmitChanges();
            }
            var result2 = (from sale in m_CosmeticDC.SalesPromotions
                           where sale.UserId == UserId
                           select sale).Single();
            return result2;
        }
        /// <summary>
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="acceptmail"></param>
        /// <param name="book"></param>
        /// <param name="children"></param>
        /// <param name="cloth"></param>
        /// <param name="cosmetic"></param>
        /// <param name="cyber"></param>
        /// <param name="house"></param>
        public void UpdateSalePromotion(Guid UserId, bool acceptmail, bool book, bool children, bool cloth, bool cosmetic, bool cyber, bool house)
        {
            var result = (from sale in m_CosmeticDC.SalesPromotions
                          where sale.UserId == UserId
                          select sale).Single();
            result.AcceptMail = acceptmail;
            result.Book = book;
            result.Children = children;
            result.Cloth = cloth;
            result.Cosmetic = cosmetic;
            result.Cyber = cyber;
            result.House = house;

            m_CosmeticDC.SubmitChanges();
        }

        public List<Message> GetMessageByUserId(Guid UserId)
        {
            List<Message> result = (from message in m_CosmeticDC.Messages
                                    where message.UserId == UserId
                                    select message).ToList();
            return result;
        }

        //public decimal GetIntegralByUserId(Guid UserId)
        //{
        //    List<Order> orders = (from ord in m_CosmeticDC.Orders
        //                          where ord.UserId == UserId && ord.Valid == true
        //                          select ord).ToList();
        //    decimal? integral ;

        //    foreach (Order item in orders)
        //    {
        //        integral += item.Intergral;
        //    }

        //    return integral;
        //}

        public IEnumerable GetIntegralDetailByUserId(Guid UserId)
        {
            //帐号 认购时间 完成时间 订单积分 
            var result = from ord in m_CosmeticDC.Orders
                         join usr in m_CosmeticDC.Users on ord.UserId equals usr.UserId
                         where ord.UserId == UserId && ord.Valid == true && ord.State=="完成"
                         select new
                         {
                             UserEmail = usr.Email,
                             CreateTime = ord.CreateTime,
                             CompleteTime = ord.CompleteTime,
                             Integral = ord.Intergral
                         };
            return result;
        }

        public IEnumerable GetIntegralUsedDetailByUserId(Guid UserId)
        {
            //序号
            //时间
            //操作的时间
            //状态
            //备注
            var result = from ord in m_CosmeticDC.Orders
                         join usr in m_CosmeticDC.Users on ord.UserId equals usr.UserId
                         where ord.UserId == UserId && ord.Valid == true && ord.UseIntegral != 0
                         select new
                         {
                             UserEmail = usr.Email,
                             CreateTime = ord.CreateTime,
                             CompleteTime = ord.CreateTime,
                             Integral = ord.UseIntegral
                         };
            return result;
        }

        static string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;

        /// <summary>
        /// 更新会员生日。
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="birthday"></param>
        public static void UpdateBirthday(string userId, DateTime birthday)
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "update [User] set Birthday = '" + birthday + "' where UserId = '" + userId + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 获取会员生日。
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="birthday"></param>
        public static DateTime GetBirthdayBtUserID(string userId)
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select Birthday from [User]  where UserId = '" + userId + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                return Convert.ToDateTime(com.ExecuteScalar());
            }
            catch
            {
                return Convert.ToDateTime("1900-1-1");
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 10.18,Get members by level.
        /// </summary>
        /// <param name="theLevel"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetMembersByLevel(string theLevel)
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select UserId,UserName,[NickName],[Level] from dbo.[User] where  [Level] ='" + theLevel + "'";
            
                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 10.18,Get members by level.
        /// </summary>
        /// <param name="theLevel"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetMembersByLevel(string theLevel, string birthday, string sex, decimal Custome, int FriendNum, string Address)
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select UserId,UserName,[NickName],[Level] from dbo.[User] where 1=1 ";
                if (!string.IsNullOrEmpty(theLevel))
                {
                    cmdStr += " and  [Level] ='" + theLevel + "'";
                }
                if (!string.IsNullOrEmpty(birthday))
                {
                    cmdStr += " and Birthday >= '" + birthday + "' and Birthday < '" + Convert.ToDateTime(birthday).AddYears(1).ToString("yyyy/MM/dd") + "' ";
                }
                if (!string.IsNullOrEmpty(sex))
                {
                    cmdStr += " and [Sex]= '" + sex + "' ";
                }
                if (Custome > 0)
                {
                    cmdStr += " and UserId in (select UserId from [Order] as ord2 where ord2.State = '完成' group by  ord2.UserId having sum(Price)> = " + Custome + " ) ";
                }
                if (!string.IsNullOrEmpty(Address))
                {
                    cmdStr += "  and UserId in (select UserId from UserInfo as ui where ui.Address like '%'+'" + Address + "'+'%') ";
                }
                if (FriendNum > 0)
                {
                    cmdStr += " and  UserId in (select us2.Referee from [User] as us2 group by us2.Referee having count(us2.Referee)>= " + FriendNum + " ) ";
                }


                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 10.18。Send message to members.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="theTitle"></param>
        /// <param name="theContent"></param>
        public static void SendMessagesToMembers(string userId, string theTitle, string theContent)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "INSERT INTO [dbo].[Message] ([Id] ,[UserId] ,[Title] ,[SendTime] ,[Sender]  ,[Content])  VALUES (newid()  ,'" + userId + "' ,'" + theTitle + "' ,getdate() ,'系统'  ,'" + theContent + "')";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();

            }
            finally
            {
                con.Close();
            }
        }

        public string GetGreatestMemberCard()
        {
            var result = (from user in m_CosmeticDC.Users
                          where 1 == 1
                          orderby user.MemberCard descending
                          select user).First();
            return result.MemberCard;
        }

        /// <summary>
        /// 10.25,Get all members .
        /// </summary>
        /// <param name="theLevel"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetMembersCard()
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "SELECT [CardNum]  ,(select [UserName] from [User] where [User].[UserId] = [CashCard].[UserId]) as [Name]  ,[ctime]  ,[balance] ,[ValidateCode] ,[ExpireTime]  FROM [dbo].[CashCard]";
                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 10.25。Delete member card.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="theTitle"></param>
        /// <param name="theContent"></param>
        public static void DeleteMemberCard(string cardId)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "delete [CashCard] where CardNum='" + cardId + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 10.25。Delete member card.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="theTitle"></param>
        /// <param name="theContent"></param>
        public static void DeleteMemberCard(List<string> strCrdNum)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                for (int i = 0; i < strCrdNum.Count; i++)
                {
                    string cmdStr = "delete [CashCard] where CardNum='" + strCrdNum[i] + "'";
                    SqlCommand com = new SqlCommand(cmdStr, con);
                    com.ExecuteNonQuery();
                }
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 10.25 得到优惠券的几个大类。
        /// </summary>
        /// <param name="id"></param>
        public static DataTable SelectTypes()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select [Name] from Category where SupCategoryId = '2D0124D3-B73F-4348-9487-9E0D1F6C9DE6' and CategoryId <> SupCategoryId";
                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 10.25。向优惠券表里插入数据。
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="theTitle"></param>
        /// <param name="theContent"></param>
        public static void InsertDiscountCard(string id, string userId, decimal amount, string type, string expireTime, decimal minConsumeAmount)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();

                string cmdStr = "INSERT INTO [dbo].[DiscountCard]([Id] ,[UserId] ,[Amount] ,[Type] ,[ExpireTime],[MinConsumeAmount],[IsUsed])  VALUES('" + id + "' ,'" + userId + "' ," + amount + " ,'" + type + "' ,'" + expireTime + "' ," + minConsumeAmount + " ,0)";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();

            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 10.25 得到有优惠券的会员。
        /// </summary>
        /// <param name="id"></param>
        public static DataTable SelectAllDiscountCard()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "SELECT [Id],(select [User].[UserName] from [User] where [User].[UserId]= [DiscountCard].[UserId]) as [Name] ,[Amount],[Type],[ExpireTime],[MinConsumeAmount],[IsUsed] FROM [dbo].[DiscountCard]";
                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 10.25。删除指定的有优惠券的会员。
        /// </summary>
        /// <param name="strNum"></param>
        public static void DeleteDiscountCard(List<string> strNum)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                for (int i = 0; i < strNum.Count; i++)
                {
                    string cmdStr = "delete [DiscountCard] where Id='" + strNum[i] + "'";
                    SqlCommand com = new SqlCommand(cmdStr, con);
                    com.ExecuteNonQuery();
                }
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 10.25。得到现在最大优惠券的号码。
        /// </summary>
        /// <param name="strNum"></param>
        public static string SelectTopNum()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select top 1 (Id) from [dbo].[DiscountCard] order by Id Desc";
                SqlCommand com = new SqlCommand(cmdStr, con);
                object num = com.ExecuteScalar();
                if (!string.IsNullOrEmpty(num.ToString()))
                {
                    return num.ToString();
                }
                else
                {
                    return "100001";
                }

            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 10.25。
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="theTitle"></param>
        /// <param name="theContent"></param>
        public static void InsertKnowIbeauty(string content)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "INSERT INTO [dbo].[KnowIbeauty] ([TheWay]) VALUES ('" + content + "')";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 10.25。Get all date.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="theTitle"></param>
        /// <param name="theContent"></param>
        public static DataTable SelectKnowIbeauty()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select Id, TheWay from [dbo].[KnowIbeauty]";
                SqlCommand com = new SqlCommand(cmdStr, con);
                DataSet ds = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 删除指定的项。
        /// </summary>
        /// <param name="id"></param>
        public static void DeleteKnowIbeauty(int id)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "delete [dbo].[KnowIbeauty] where Id = " + id + "";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 更新会员生日,及其它内容。
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="birthday"></param>
        public static void UpdateBirthday(string userId, DateTime birthday, string nickName, string level, int sex, string email)
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "UPDATE [dbo].[User]  SET [NickName] = '" + nickName + "',[Level] = '" + level + "',[Sex] = " + sex + ",[Birthday] =  '" + birthday + "',[Email] = '" + email + "' where UserId = '" + userId + "' ";

                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 更新会员其它内容。
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="birthday"></param>
        public static void UpdateMemberInfor(string userId, string realName, string province, string city,
            string area, string idCard, string homeTel, string mobileTel, string companyAddress, string postCode)
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "UPDATE [dbo].[UserInfo]  SET [RealName] = '" + realName + "' ,[Province] = '" + province + "' ,[City] = '" + city + "' ,[Area] = '" + area + "' ,[IdCard] = '" + idCard + "' ,[HomeTel] = '" + homeTel + "' ,[MobileTel] = '" + mobileTel + "' ,[CompanyAddress] = '" + companyAddress + "' ,[PostCode] = '" + postCode + "' where UserId='" + userId + "'";

                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 10.25。Insert member card.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="theTitle"></param>
        /// <param name="theContent"></param>
        public static void InsertMemberCard(string CardNum, string userId, string validateCode, decimal faceValue, string expireTime)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();

                string cmdStr = "INSERT INTO  [dbo].[CashCard]  ([CardNum]  ,[UserId]  ,[ctime] ,[balance] ,[ValidateCode] ,[ExpireTime] ,[FaceValue]) VALUES ('" + CardNum + "'    ,null ,getdate() ," + faceValue + "  ,'" + validateCode + "' ,'" + expireTime + "' ," + faceValue + ")";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();

            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// 10.25,Get all members .
        /// </summary>
        /// <param name="theLevel"></param>
        /// <returns></returns>
        public static string GetMembersTheBigCard()
        {
            // string conStr = ConfigurationManager.ConnectionStrings["CosmeticConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "select top 1 convert(decimal, CardNum ) from dbo.CashCard order by convert(decimal, CardNum ) desc";
                SqlCommand com = new SqlCommand(cmdStr, con);
                object num = com.ExecuteScalar();
                if (num == null)
                {
                    return "0";
                }
                else
                {
                    return num.ToString();
                }
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 更新会员的积分。
        /// </summary>
        public static void UpdateIntegral(string userid,int integral)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                string cmdStr = "update  dbo.[User] set Integral = Integral + " + integral + "  where UserId= '" + userid + "'";
                SqlCommand com = new SqlCommand(cmdStr, con);
                com.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

    }

}
